package com.cinyi.ormlite.web.util;

import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cinyi.ormlite.annotation.AnnotationNotFoundException;
import com.cinyi.ormlite.annotation.TConstrains;
import com.cinyi.ormlite.annotation.TField;
import com.cinyi.ormlite.annotation.TTable;

public class DbUtil {
//	private static  Logger logger=Logger.getLogger(DbUtil.class);
	
	public static final int MAX_COUNT = 3 ;
	
	 /**
     *  依次关闭ResultSet、Statement、Connection
     *  若对象不存在则创建一个空对象
     * @param rs
     * @param st
     * @param rs
     * @param conn
     */
    public static void close(ResultSet rs,Statement st,Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally{
                if(st != null){
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    } finally{
                        if(conn != null){
                            try {
                                conn.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
    }

    public static void close(ResultSet rs,Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }  
        }
        closeConnection(conn);
    }
    
    public static void close(Statement st,Connection conn){
        if(st != null ){
            try {
            	st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } 
        }
        closeConnection(conn);
    }
    public static void closeConnection(Connection conn){
        if(conn != null){
            try {
            	conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } 
        }
    }
    
    /**
     * 新增、修改、删除、查询记录(也可以改为有结果集ResultSet返回的查询方法)
     * @param sql
     * @throws  
     */
    public static void execute(Connection conn,String sql){
        PreparedStatement pst = null;
        try {
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);
            pst.execute();
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally{
            closeConnection(conn); 
        }
    }
    
    public static void insert(Connection conn,String sql,List<String> params){
        PreparedStatement pst = null;
        try {
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sql);
            if(params != null && params.size()>0){
            	int index = 1 ;
            	for(String val : params){
            		pst.setString(index, val);
            		index ++ ;
            	}
            }
            pst.execute();
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally{
            closeConnection(conn); 
        }
    }
 
    public static void executeProcedure(Connection con, String sql) {
        CallableStatement callStmt = null;
        try {
                callStmt = con.prepareCall(sql);
                callStmt.execute();
        } catch (Exception e) {
                e.printStackTrace();
        }

    }
    
    public static ResultSet executeQuery(Connection conn,String sql) {
        PreparedStatement pst = null;
        ResultSet rs = null ;
        try {
            if(conn!=null){
                pst = conn.prepareStatement(sql);
                rs = pst.executeQuery();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }  
        
        return rs ;
    }

    public static Long count(Connection conn,String sql) throws SQLException {
    	Long result = 0L ;
        PreparedStatement pst = null;
        ResultSet rs = null ;
        try {
            if(conn!=null){
                pst = conn.prepareStatement(sql);
                rs = pst.executeQuery();
                if(rs.next()){
                	return rs.getLong(1);
                }
            }
        } finally{
        	closeConnection(conn);
        }
        
        return result ;
    }

    /********************************************************/

    /***
     * 根据参数查询sql的方法
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static ResultSet executeQuery(Connection conn,String sql,List<Object> params) {
        PreparedStatement pst = null;
        ResultSet rs = null ;
        try {
            if(conn!=null){
                pst = conn.prepareStatement(sql);
                if(params != null ){
	                for (int i=0; i<params.size();i++){
	                    pst.setObject(i+1,params.get(i));
	                }
                }
                rs = pst.executeQuery();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs ;
    }
    /***
     * 根据参数查询记录数目
     * @param conn
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static Long count(Connection conn,String sql,List<Object> params) throws SQLException {
        Long result = 0L ;
        PreparedStatement pst = null;
        ResultSet rs = null ;
        try {
            if(conn!=null){
                pst = conn.prepareStatement(sql);
                if( params != null){
                	for (int i=0; i<params.size();i++){
                        pst.setObject(i+1,params.get(i));
                    }
                }
                
                rs = pst.executeQuery();
                if(rs.next()){
                    return rs.getLong(1);
                }
            }
        } finally{
            closeConnection(conn);
        }

        return result ;
    }
    
    /***
     * 根据sql查询list
     * @param sql
     * @param dbName
     * @return
     */
    @SuppressWarnings("rawtypes")
    public static List<Map> queryListBySql(Connection conn,String sql,String dbName,List params){
        List<Map> list=new ArrayList<Map>();
        ResultSet resultSet = null ;
        try{
            resultSet= DbUtil.executeQuery(conn,sql,params);
            ResultSetMetaData m=resultSet.getMetaData();
            int columns=m.getColumnCount();
            //显示表格内容
            while(resultSet.next())
            {
                Map map=new HashMap();
                for(int i=1;i<=columns;i++)
                {
                    map.put(m.getColumnLabel(i),resultSet.getString(i));
                }
                list.add(map);
            }
            resultSet.close();
        }catch (Exception e){
        }finally {
            DbUtil.closeConnection(conn);
        }
        return list;
    }
    /***
     * 没有参数的sql查询
     * @param sql
     * @param dbName
     * @return
     */
    public static List<Map> queryListBySql(Connection conn,String sql,String dbName){
        List<Map> list=new ArrayList<Map>();
        ResultSet resultSet = null ;
        try{
            resultSet= DbUtil.executeQuery(conn,sql);
            ResultSetMetaData m=resultSet.getMetaData();
            int columns=m.getColumnCount();
            //显示表格内容
            while(resultSet.next())
            {
                Map map=new HashMap();
                for(int i=1;i<=columns;i++)
                {
                    map.put(m.getColumnLabel(i),resultSet.getString(i));
                }
                list.add(map);
            }
            resultSet.close();
        }catch (Exception e){
        }finally {
            DbUtil.closeConnection(conn);
        }
        return list;
    }
    /***
     * 根据sql查询数目
     * @param sql
     * @param dbName
     * @return
     */
    public static Long queryCountBySql(Connection conn,String dbName,String sql,List params){
        ResultSet resultSet = null ;
        Long count=1L;
        try{
            count=count(conn,sql,params);
        }catch (Exception e){
//            logger.info(e.getMessage());
        }finally {
            DbUtil.closeConnection(conn);
        }
        return count;
    }
    
	/**
	 * 释放数据库资源
	 */
	public static void release(Connection conn,PreparedStatement ps, ResultSet rs) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
			if (ps != null) {
				ps.close();
				ps = null;
			}
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void release(PreparedStatement ps, ResultSet rs) {
		try {
			if (ps != null) {
				ps.close();
				ps = null;
			}
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void release(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException e) {
        }
    }

    public String dropTableScript(Class<?> clazz) throws Exception {
        if(!clazz.isAnnotationPresent(TTable.class)){
            throw new NullPointerException("the table not found:" + clazz.getSimpleName());
        }
        String tableName = getTableName(clazz);
        String sqlCreat = "DROP TABLE `"+ tableName +"` \n";  
        return sqlCreat ;
    }
    
    public String createTableScript(Class<?> clazz) throws Exception {
        if(!clazz.isAnnotationPresent(TTable.class)){
            throw new NullPointerException("the table not found:" + clazz.getSimpleName());
        }
        String tableName = getTableName(clazz);
        //存储所有字段的信息
        //通过反射获得要查询的字段
        StringBuffer createCommond = new StringBuffer();
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(TField.class)) {
                TField tfield = field.getAnnotation(TField.class);
                String idFieldName = tfield.name();
                createCommond.append(" `").append(idFieldName).append("` ");
                Class<?> clazzField = tfield.type() ;
                if(clazzField == String.class && tfield.length() == 0){
                    throw new NullPointerException("varcahr's lenght required:" + idFieldName );
                }

                String paramVal = "" ;
                if (clazzField == String.class) {
                    paramVal = " varchar " ;
                } else if (clazzField == short.class || clazzField == Short.class||clazzField == int.class || clazzField == Integer.class || clazzField == long.class || clazzField == Long.class || clazzField == byte.class || clazzField == Byte.class ) {
                    paramVal = " int " ;
                } else if (clazzField == Date.class) {
                    paramVal = "date" ;
                } else if (clazzField == Timestamp.class) {
                    paramVal = "timestamp" ;
                } else {
                    createCommond.append(" ").append(tfield.type().getSimpleName().toLowerCase());
                } 
                createCommond.append(paramVal) ;

                if(tfield.length()>0){
                    createCommond.append("(").append(tfield.length()).append(") ") ;
                }
                
                TConstrains tc = tfield.constrains();
                createCommond.append(getConstrains(tc));
                createCommond.append("\n,");
            }
        }
        createCommond.deleteCharAt(createCommond.length()-1);
        
         String sqlCreat = "CREATE TABLE IF NOT EXISTS `"+ tableName +"` ( \n";  
         sqlCreat += createCommond.toString() +" \n ) ";  
        return sqlCreat ;
    }
    
    /** 
     * 获得表名
     */
    private String getTableName(Class<?> clazz) throws AnnotationNotFoundException {
        if (clazz.isAnnotationPresent(TTable.class)) {
            TTable entity = clazz.getAnnotation(TTable.class);
            return entity.value();
        } else {
            throw new AnnotationNotFoundException(clazz.getName() + " is not Table Annotation.");
        }
    }
    
     private static String getConstrains(TConstrains constrain) {  
        String rst = "";  
        if(constrain.notNull()){  
            rst += " not null ";  
        }  
        if(constrain.primaryKey()){  
            rst += " primary key ".toLowerCase();  
        }  
        if(constrain.unique()){  
            rst += " unique ".toLowerCase();  
        } 
        
        if(constrain.auto()){  
            rst += " AUTO_INCREMENT ".toLowerCase();  
        } 
        return rst;  
    } 
     
	
}
